﻿CREATE VIEW dbo.v_Note
AS
SELECT     N.ID, N.LoanID, N.Note, N.StatusID, S.Name AS Status, N.StatusDate, N.StatusUser, NAC.Name AS NoteActionCode, NT.Name AS NoteType, N.DateDue, 
                      N.DateDueBy, N.NoteTypeID, N.NoteActionCodeID, CASE WHEN N .StatusID = dbo.fs_StateID__Notes__PromisedToPay() THEN datediff(dd, GETDATE(), 
                      DateDue) ELSE NULL END AS DaysToPTP, N.Phone, N.PhoneCategoryID, ISNULL(NT.Nick, NT.Name) AS NoteTypeNick, ISNULL(NAC.Nick, NAC.Name) 
                      AS NoteActionCodeNick, ISNULL(S.Nick, S.Name) AS StatusNick, N.IsTask, N.NoteDate, N.IsResolved, NAC.ImageURL AS NoteActionCodeImageURL, 
                      N.NoteUser, COALESCE (NT.IconURL, NAC.ImageURL, S.Icon) AS NoteTypeIconURL, LEFT(N.Note, 40) AS Note_Short, N.NoteDate0, N.NoteDate1, 
                      usix.t_CustomerFile_Export.UsixID
FROM         dbo.t_Note AS N INNER JOIN
                      dbo.ts_NoteActionCode AS NAC ON N.NoteActionCodeID = NAC.ID INNER JOIN
                      dbo.ts_NoteType AS NT ON N.NoteTypeID = NT.ID INNER JOIN
                      dbo.t_State AS S ON N.StatusID = S.ID LEFT OUTER JOIN
                      usix.t_CustomerFile_Export ON N.LoanID = usix.t_CustomerFile_Export.LoanID

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[28] 4[27] 2[5] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "N"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 264
               Right = 207
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "NAC"
            Begin Extent = 
               Top = 312
               Left = 154
               Bottom = 435
               Right = 351
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "NT"
            Begin Extent = 
               Top = 134
               Left = 438
               Bottom = 241
               Right = 589
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "S"
            Begin Extent = 
               Top = 340
               Left = 367
               Bottom = 551
               Right = 518
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_CustomerFile_Export (usix)"
            Begin Extent = 
               Top = 6
               Left = 288
               Bottom = 114
               Right = 487
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 29
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_Note';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'= 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 10275
         Alias = 2190
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_Note';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_Note';

